# encoding:utf-8
from decimal import Decimal
from flask import g, session
from models._base import conn
from sql.store import IndexDataStoreHandleSQL, ProductOrderDetailsStoreHandleSQL, AbnormalOperationSQL, \
    TimeProductStatisticsSQL, PaymentProportionSearchSQL, SaleCompareWithLastWeekSQL, \
    StoreFinancialOperationAnalysisSQL, \
    GiveProductSaleSQL, SearchRetreatProductSQL
from sql.user import SearchUserByUserIdPwdSQL, SearchAllUsersStoreHandleSql, \
    SearchUserPhoneSettingSQL, UserRechargeTotalSQL, SearchUserBrandStoreSQL, EmployeeCommissionSQL


class PageData(object):
    def __init__(self, user, store_id):
        self.user = user
        self.store_id = store_id

    @classmethod
    def user_login(cls, **kwargs):
        """登录"""
        user_data_list = conn.ExecQuery(SearchUserByUserIdPwdSQL.format(**kwargs))
        column_name_dict = {
            0: "ID",
            1: "UserName",
            2: "UserID",
            3: "UserPwd",
            4: "RoleID",
            5: "TelePhone",
            6: "StoreID",
            7: "BrandID",
            8: "CreateUer",
            9: "CreateTime",
            10: "State",
            11: "Sex",
            12: "Remarks",
            13: "Note1",
            14: "Note2",
            15: "Note3",
            16: "Note4",
            17: "Note5",
            18: "CompanyID",
            19: "OpenID"
        }
        if len(user_data_list) > 0:
            user_info = user_data_list[0]
            session['User'] = {}

            for index, column_name in column_name_dict.items():
                session['User'][column_name] = user_info[index]

            conn.conn.close()
        return session['User']

    @classmethod
    def get_user_brand_store(cls, **kwargs):
        """获取用户的品牌及门店"""
        query = conn.ExecQuery(SearchUserBrandStoreSQL.format(**kwargs))
        column_name_dict = {
            0: "BrandID",
            1: "BrandName",
            2: "StoreID",
            3: "StoreName"
        }
        shops = {}
        data_list = []
        if len(query) > 0:
            for data in query:

                main_data = {}
                for index, column_name in column_name_dict.items():
                    main_data[column_name] = data[index]

                data_list.append(
                    main_data
                )

                if main_data['BrandID'] not in shops:

                    shops[main_data['BrandID']] = {"name": main_data['BrandName'], "shops": [
                        {"id": main_data['StoreID'],
                         "name": main_data['StoreName']}
                    ]}
                else:
                    shops[main_data['BrandID']]['shops'].append({
                        "id": main_data['StoreID'],
                        "name": main_data['StoreName']
                    })
        print shops
        return shops

    @classmethod
    def get_user_permission(cls, user):
        """获取用户权限"""

        role_permission = conn.ExecQuery(SearchAllUsersStoreHandleSql.format(**{
            "RoleID": user['RoleID']
        }))
        column_name_dict = {}
        return role_permission

    @classmethod
    def main_data_store_statistic(cls, **kwargs):
        """
        主营业数据
        字典id，已结金额，应收总额，未结金额，用餐人数，桌均消费，人均消费，昨日金额，本月总额
        """
        main_data_list = conn.ExecQuery(IndexDataStoreHandleSQL.format(**kwargs))
        main_data = []
        column_name_dict = {
            0: "DictionaryID",
            1: u"已结金额",
            2: u"应收总额",
            3: u"未结金额",
            4: u"用餐人数",
            5: u"桌均消费",
            6: u"人均消费",
            7: u"昨日金额",
            8: u"本月总额"
        }
        if len(main_data_list) > 0:
            data = main_data_list[0]

            main_data = {}
            for index, column_name in column_name_dict.items():
                main_data[column_name] = data[index]

        return main_data

    # 支付占比
    @classmethod
    def get_payment_proportion_data(cls, **kwargs):
        """
        支付占比统计
        SP_PaymentProportion_Get

        """
        main_data_list = conn.ExecQuery(PaymentProportionSearchSQL.format(**kwargs))

        column_name_dict = {
            0: u"支付方式",
            1: u"今日支付金额",
            2: u"今日券类实收金额",
            3: u"今日单数",
            4: u"上周支付金额",
            5: u"上周券类实收金额",
            6: u"上周单数"
        }

        data_list = []
        payment_total = 0
        if len(main_data_list) > 0:
            for data in main_data_list:
                main_data = {}
                for index, column_name in column_name_dict.items():
                    if isinstance(data[index], Decimal):

                        main_data[column_name] = float(data[index])
                    else:
                        main_data[column_name] = data[index]
                payment_total += main_data[u'今日支付金额']
                data_list.append(
                    main_data
                )

        return payment_total, data_list

    @classmethod
    def product_sale_data(cls, **kwargs):
        """
        销售数据

        """

        data_list = conn.ExecQuery(ProductOrderDetailsStoreHandleSQL.format(**kwargs))
        sale_data = []
        column_name_dict = {
            0: u"序号",
            1: u"点菜员",
            2: u"菜品大类",
            3: u"菜品小类",
            4: u"菜品",
            5: u"数量",
            6: u"规格",
            7: u"重量",
            8: u"金额",
            9: u"单价",
            10: u"定额提成",
            11: u"比例提成"
        }
        category_percent = {}
        sale_total = 0
        for data in data_list:

            main_data = {}
            for index, column_name in column_name_dict.items():
                main_data[column_name] = data[index]
            if main_data[u'菜品小类'] not in category_percent.keys():
                category_percent[main_data[u'菜品小类']] = 0
            else:
                category_percent[main_data[u'菜品小类']] += main_data[u'金额']
            sale_data.append(
                main_data
            )
        sale_total = float(sum([v for i, v in category_percent.items()]))
        sale_data_percent = []
        new_sale_percent = []
        if sale_total > 0:

            for k, v in category_percent.items():
                sale_data_percent.append(
                    {u"菜品小类": k,
                     u"金额": str(v),
                     u"百分比": str(round((float(v) / sale_total), 3) * 100)})
            new_sale_percent = sorted(sale_data_percent, key=lambda d: d[u'百分比'], reverse=True)[0:5]
            other_sale_total = sale_total - sum([float(i[u'金额']) for i in new_sale_percent])
            new_sale_percent.append(
                {u"菜品小类": u'其他',
                 u"金额": other_sale_total,
                 u"百分比": str(round((other_sale_total / sale_total), 3) * 100)}
            )
            conn.conn.close()
        return sale_total, new_sale_percent, \
               sorted(sale_data, key=lambda d: d[u'数量'],
                      reverse=True)

    # 异常操作
    @classmethod
    def get_abnormal_operation_data(cls, **kwargs):
        """
        异常操作
        T_ReportStoreHandleAbnormalOperationDetails
        """
        print AbnormalOperationSQL.format(**kwargs)
        main_data_list = conn.ExecQuery(AbnormalOperationSQL.format(**kwargs))

        column_name_dict = {
            0: u"序号",
            1: u"账单号",
            2: u"操作员",
            3: u"异常类型",
            4: u"差额",
            5: u"操作原因",
            6: u"操作明细",
            7: u"操作时间",
            8: u"StoreID"
        }

        data_list = []
        total = 0
        if len(main_data_list) > 0:
            for data in main_data_list:
                main_data = {}
                for index, column_name in column_name_dict.items():
                    main_data[column_name] = data[index]
                total += main_data[u'差额']
                data_list.append(
                    main_data
                )

        return total, data_list

    @classmethod
    def get_retreat_product_data(cls, **kwargs):
        """退品详情"""
        # TODO 待优化
        main_data_list = conn.ExecQuery(SearchRetreatProductSQL.format(**kwargs))
        data_list = []
        total = 0
        for d in data_list:
            money = float(d[8].replace("￥", "").replace("元", ""))
            data_list.append({
                "order_number": d[5],
                "goods_name": d[3],
                "count": d[7],
                "unit_price": money / float(d[7]),
                "money": d[8],
                "handler_name": d[10],
            })
            total += money
        return total, data_list

    @classmethod
    def get_product_time_sale_statistics_by_time(cls, **kwargs):
        """时段统计
        SP_Report_TimeProductStatistics
        """

        main_data_list = conn.ExecQuery(TimeProductStatisticsSQL.format(**kwargs))
        data_list = []
        column_name_dict = {
            0: u"序号",
            1: u"时间区间",
            2: u"菜品",
            3: u"总量",
            4: u"规格",
            5: u"总额"
        }

        if len(main_data_list) > 0:
            for data in main_data_list:

                main_data = {}
                for index, column_name in column_name_dict.items():
                    main_data[column_name] = data[index]

                data_list.append(
                    main_data
                )
        return sorted(data_list, key=lambda d: d[u'总量'],
                      reverse=True)

    # 营业日报
    @classmethod
    def get_store_day_financial_data(cls, **kwargs):
        """
        SP_Report_StoreFinancial_OperationAnalysis_Function
        营业汇总
        会员，
        虚收
        """
        # TODO "Divide by zero error encountered.DB-Lib"
        try:
            main_data_list = conn.ExecQuery(StoreFinancialOperationAnalysisSQL.format(**kwargs))
        except Exception, e:
            print e
            main_data_list = []
        data_list = {
            u"营业汇总": {
                u"菜品销售": 0,
                u"服务费": 0,
                u"合计": 0,

            },
            u"会员": {
                u"会员充值": 0,
                u"会员消费": 0,
                u"合计": 0,

            },
            u"虚收": {
                u"折扣": 0,
                u"促销优惠": 0,
                u"免单": 0,
                u"赠送": 0,
                u"改价": 0,
                u"抹零": 0,
                u"代金券需收": 0
            }
        }
        column_name_dict = {
            0: "DictionaryID",
            1: "ParentID",
            2: "KeyName",
            3: "KeyValue",
            4: "StoreID",
            5: "CreateTime"

        }

        if len(main_data_list) > 0:
            for data in main_data_list:
                main_data = {}
                for index, column_name in column_name_dict.items():
                    main_data[column_name] = data[index]
                if main_data['ParentID'] in data_list.keys():
                    if main_data['KeyName'] in data_list[main_data['ParentID']]:
                        data_list[main_data['ParentID']][main_data['KeyName']] = main_data['KeyValue']
        return data_list

    @classmethod
    def get_recharge_total_data(cls, **kwargs):
        """会员充值情况"""
        main_data_list = conn.ExecQuery(UserRechargeTotalSQL.format(**kwargs))
        column_name_dict = {
            0: "Number",
            1: "MemberID",
            2: "MemberName",
            3: "MemberCard",
            4: "MemberPhone",
            5: "CardTypeID",
            6: "Balance",
            7: "TopupBalance",
            8: "GiveBalance",
            9: "Integral",
            10: "AddupTopup",
            11: "AddupGive",
            12: "AddupConsumption",
            13: "AddupConsumptionNum",
            14: "PaymentID",
            15: "PaymentName",
            16: "AddupDiscount",
            17: "Statue",
            18: "RecentlyTime",
            19: "OpenCardTime",
            20: "StoreID",
            21: "BrandID",
        }
        total = 0
        data_list = []
        if len(main_data_list) > 0:
            for data in main_data_list:

                main_data = {}
                for index, column_name in column_name_dict.items():
                    main_data[column_name] = data[index]
                total += float(main_data['TopupBalance'])
                data_list.append(
                    main_data
                )

        return total, data_list

    @classmethod
    def get_employee_commission(cls, **kwargs):
        """员工提成"""
        # TODO
        data_list = []
        total = 0
        query = conn.ExecQuery(EmployeeCommissionSQL.format(**kwargs))
        for d in query:
            data_list.append({
                'id': d[1],
                'name': d[2],
                'commission': d[11],
                'count': d[6],
            })
            total += float(d[11].replace("￥", "").replace("元", ""))
        return total, data_list

    @classmethod
    def get_sale_compare_with_lastweek(cls, **kwargs):
        """上周同业对比
        T_ReportHomePage_StStore
        """

        main_data_list = conn.ExecQuery(SaleCompareWithLastWeekSQL.format(**kwargs))

        main_data = {}
        column_name_dict = {
            0: u"DictionaryID",
            1: u"门店ID",
            2: u"已结金额",
            3: u"应收总额",
            4: u"未结金额",
            5: u"用餐人数",
            6: u"桌均消费",
            7: u"人均消费",
            8: u"昨日金额",
            9: u"订单数",
            10: u"免单数量",
            11: u"赠菜数量",
            12: u"翻台率",
            13: u"上座率",
            14: u"本月总额",
            15: u"创建日期"
        }
        if len(main_data_list) > 0:
            data = main_data_list[0]
            main_data = {}
            for index, column_name in column_name_dict.items():
                main_data[column_name] = data[index]

        return main_data

    @classmethod
    def get_give_product_sale_data(cls, **kwargs):
        """菜品赠送"""

        main_data_list = conn.ExecQuery(GiveProductSaleSQL.format(**kwargs))
        data_list = []
        column_name_dict = {
            0: "STOREID",
            1: "BRANDID",
            2: "CHECKOUTTIME",
            3: "BILL_CREATETIME",
            4: "CONTENT",
            5: "PRICE",
            6: "STOCK",
            7: "CREATETIME"
        }
        total = 0
        if len(main_data_list) > 0:
            for data in main_data_list:

                main_data = {}
                for index, column_name in column_name_dict.items():
                    main_data[column_name] = data[index]
                total += float(main_data['PRICE']) * float(main_data['STOCK'])
                data_list.append(
                    main_data
                )

        return total, data_list

    @classmethod
    def get_user_phone_setting(cls, **kwargs):
        """获取用户手机设置"""
        query = conn.ExecQuery(SearchUserPhoneSettingSQL.format(**kwargs))
        column_name_dict = {
            0: u"ID",
            1: u"YNAbnormal",
            2: u"YNVerification",
            3: u"YNLogon",
            4: u"YNBusiness",
            5: u"UserID"
        }
        main_data = {}
        if len(query) > 0:
            data = query[0]

            main_data = {}
            for index, column_name in column_name_dict.items():
                main_data[column_name] = data[index]

        return main_data
